package com.six.work.mapper;

import com.six.work.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PositionMapper {
    @Select("SELECT * FROM position;")
    List<Position> findAllPosition();

    //index1.html 为你匹配
    @Select("SELECT position.*,company.* FROM it.position join it.company on position.company_id=company.company_id where position.position_state='审核通过' order by position_id limit 5;")
    List<Position> findPositionAndCompany();

    //index1.html 最新职位
    @Select("SELECT position.*,company.* FROM it.position join it.company on position.company_id=company.company_id where position.position_state='审核通过' order by position.create_date desc limit 5;")
    List<Position> findPositionAndCompany1();

    //index1.html 热门公司
    @Select("select count(*) as count,company.* from it.company join it.post on post.company_id=company.company_id where position.position_state='审核通过' group by company.company_id order by count(*)desc limit 5;")
    List<CompanyAndPositionView> findPositionAndCompany2();

    @Select("select *,state.state from position join state on position.position_state=state.state where position.company_name like '%${positionName}%' and state.state_id=#{state_id} order by position.create_date desc;")
    List<PositionView> finPositionByStateAndcomName(@Param("positionName")String positionName, @Param("state_id")Integer state_id);

    @Select("select *,state.state from  position join state on position.position_state=state.state where position.company_name like '%${positionName}%' order by position.create_date desc;")
    List<PositionView> findPositionByName(String positionName);


    @Update("UPDATE position SET `position_state` = '审核未过' \n" +
            "WHERE (position_id = #{position_id})")
    String updatePositionState(Integer position_id);

    @Update("UPDATE position SET `position_state` = '审核通过' \n" +
            "WHERE (position_id = #{position_id})")
    String updatePositionState1(Integer position_id);

    @Select("select * from position where position_id=#{position_id}")
    Position SelectPositionByID(Integer position_id);

    //连数据库
    @Select("SELECT position.*,company.* FROM position join company on position.company_id=company.company_id where position_state='审核通过'")
    List<Position> findAllPosition1();


    //按工作地点查询
    @Select("select position.*,company.* FROM position join company on position.company_id=company.company_id where company.job_add like '%${job_add}' and position_state='审核通过'")
    List<Position> findPositionByAdd(String job_add);

    //按细分方向查询
    @Select("SELECT position.*,company.* FROM position join company on position.company_id=company.company_id where job_type like '%${job_type}%' and position_state='审核通过'")
    List<Position> findPositionByType(String job_type);

    //按薪资查询
    @Select("SELECT position.*,company.* FROM position join company on position.company_id=company.company_id where job_pay like '%${job_pay}%' and position_state='审核通过'")
    List<Position> findPositionByPay(String job_pay);

    //发布职位
    @Select("SELECT * FROM position")
    List<Position> findAllPositions();

    @Insert("insert into position (position_id,job,job_type,job_pay,company_id,company_name,company_type,experience,education,job_duties,company_site,company_time) values (#{position_id},#{job},#{job_type},#{job_pay},#{company_id},#{company_name},#{company_type},#{experience},#{education},#{job_duties},#{company_site},#{company_time});")
    Integer insertPosition(Position position);

    @Select("SELECT distinct job_type FROM it.position;")
    List<Position> findComJobType();

    //审核通过公司
    @Select("select distinct position.company_id from position join company on position.company_id=company.company_id where company.company_state='审核通过'")
    List<CompanyState> findSuccessCom();


    //前端页面间Aworkposition传值到positionItem(试)
    @Select("SELECT position.*,company.* FROM position join company on position.company_id=company.company_id where position_id=#{position_id}")
    List<CompanyAndPositionView> SelectPositionByID2(Integer position_id);

    //11——13
    @Select("SELECT * FROM position where company_id=#{company_id}")
    List<Position> SelectPositionByID3(Integer company_id);

//    @Select("")

    //今日新增
    @Select("select count(*) as TodaysumPosition from position where date(create_date) = curdate();")
    Integer FindTodayCom();

    //本月新增
    @Select("select count(*) as MonthsumPosition from position where date_format(create_date,'%Y-%m')=date_format(now(),'%Y-%m')")
    Integer FindMonthCom();

    //职位 饼图
    @Select("select job_type,sum(count(*)) over (order by job_type) as jobtype from position group by job_type;")
    List<Position> sumJobType();

    //热门投递职位TOP 10
    @Select("select job_type,sum(count(*)) over (order by job_type) as jobTypeSum from post join position on post.position_id=position.position_id group by job_type order by jobTypeSum desc limit 10;")
    List<Position> jobTop10();

    //查看公司发布的职位
    @Select("select * from position where position_state='审核通过' and company_id=#{company_id}")
    List<Position> ComPosition(Integer company_id);

    @Delete("DELETE from position where position_id=#{position_id}")
    Integer DelPositionByID(Integer position_id);
}

